package com.roarkry.styloid;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class StyloidDbAdapter {
	
	private static final String COLOR_TABLE = "colors";
    public static final String KEY_COLOR_TITLE = "title";
    public static final String KEY_COLOR_RGB = "rgb";
    public static final String KEY_ROWID = "_id";
    
    private static final String ARTICLE_TABLE = "articles";
    private static final String KEY_ARTICLE_TITLE = "title";

    private static final String TAG = "StyloidDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private final Context mCtx;

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public StyloidDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the styloid database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public StyloidDbAdapter open() throws SQLException {
    	Log.w(TAG, "Opening Database");
    	mCtx.deleteDatabase("styloid");
    	mDbHelper = new DatabaseHelper(mCtx);
    	mDbHelper.initializeDataBase();
        mDb = mDbHelper.getReadableDatabase();
        return this;
    }

    /**
     * Closes the database
     */
    public void close() {
        mDbHelper.close();
    }

    /**
     * Return a Cursor over the list of all colors in the database
     * 
     * @return Cursor over all colors
     */
    public Cursor fetchAllColors() {
    	Log.w(TAG, "Fetching all Colors");
        return mDb.query(COLOR_TABLE, new String[] {KEY_ROWID, KEY_COLOR_TITLE,
                KEY_COLOR_RGB}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the color that matches the given rowId
     * 
     * @param rowId id of color to retrieve
     * @return Cursor positioned to matching color, if found
     * @throws SQLException if color could not be found/retrieved
     */
    public Cursor fetchColor(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, COLOR_TABLE, new String[] {KEY_ROWID,
                    KEY_COLOR_TITLE, KEY_COLOR_RGB}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * Return a Cursor positioned at the color that matches the given title
     * 
     * @param rowId id of color to retrieve
     * @return Cursor positioned to matching color, if found
     * @throws SQLException if color could not be found/retrieved
     */
    public Cursor fetchColorByTitle(String title) throws SQLException {

        Cursor mCursor =

            mDb.query(true, COLOR_TABLE, new String[] {KEY_ROWID,
                    KEY_COLOR_TITLE, KEY_COLOR_RGB}, KEY_COLOR_TITLE + "=?", new String[]{title},
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return a Cursor positioned at the color that matches the given RGB
     * 
     * @param rowId id of color to retrieve
     * @return Cursor positioned to matching color, if found
     * @throws SQLException if color could not be found/retrieved
     */
    public Cursor fetchColorByRGB(String RGB) throws SQLException {

        Cursor mCursor =

            mDb.query(true, COLOR_TABLE, new String[] {KEY_ROWID,
                    KEY_COLOR_TITLE, KEY_COLOR_RGB}, KEY_COLOR_RGB + "=?", new String[]{RGB},
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    /**
     * Returns the name of the color that matches the given rowId
     * 
     * @param rowId id of color to retrieve
     * @return String of color, if found
     * @throws SQLException if row could not be found
     */
    public String getColorName(int rowId) throws SQLException{
    	Cursor c = fetchColor(rowId);
    	return c.getString(c.getColumnIndex(KEY_COLOR_TITLE));
    }
    
    /**
     * Returns the name of the color that matches the given RGB
     * 
     * @param RGB of color to retrieve - must be exact match
     * @return String of color, if found
     * @throws SQLException if RGB could not be found
     */
    public String getColorName(String RGB) throws SQLException{
    	Cursor c = fetchColorByRGB(RGB);
    	return c.getString(c.getColumnIndex(KEY_COLOR_TITLE));
    }
    
    /**
     * Returns the difference between two RGB values
     * 
     * @param color1 	one color
     * @param color2	another color to compare to color1
     * @return int of difference
     */
    public int difference(String color1, String color2){
    	int red1 = Integer.parseInt(color1.substring(0,2),16);
    	int green1 = Integer.parseInt(color1.substring(2,4),16);
    	int blue1 = Integer.parseInt(color1.substring(4,6),16);
    	
    	int red2 = Integer.parseInt(color2.substring(0,2),16);
    	int green2 = Integer.parseInt(color2.substring(2,4),16);
    	int blue2 = Integer.parseInt(color2.substring(4,6),16);
    	
    	return Math.abs(red1-red2) + Math.abs(green1-green2) + Math.abs(blue1-blue2);
    }
    
    /**
     * Return an RGB value of the closest color.
     * 
     * Iterates over every color in the database and finds the closest match
     * 
     * @return RGB value of the closest color
     */
    public String interpretColor(String color){
    	if(color.contains("#"))
    		color = color.substring(1);
    	if(color.length() != 6)
    		return null;
    	
    	//check this color against every color in the database
    	Cursor c = this.fetchAllColors();
    	c.moveToFirst();
    	int minDiff = 256*3;
    	String minRGB = "";
        while (c.isAfterLast() == false) {
            String rgb = c.getString(c.getColumnIndex(KEY_COLOR_RGB));
            int diff = difference(color, rgb);
            if(minDiff > diff){
            	minDiff = diff;
            	minRGB = rgb;            	
            }
            c.moveToNext();
        }
        c.close(); //don't leave a cursor open!
        
        return minRGB;
    }
    
    /**
     * Return a Cursor over the list of all articles in the database
     * 
     * @return Cursor over all articles
     */

	public Cursor fetchAllAticles() {
		Log.w(TAG, "Fetching all Articles");
        return mDb.query(ARTICLE_TABLE, new String[] {KEY_ROWID, KEY_ARTICLE_TITLE}, null, null, null, null, null);
	}
	
	/**
     * Return a Cursor positioned at the color that matches the given rowId
     * 
     * @param rowId id of color to retrieve
     * @return Cursor positioned to matching color, if found
     * @throws SQLException if color could not be found/retrieved
     */
    public Cursor fetchArticle(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, ARTICLE_TABLE, new String[] {KEY_ROWID,
                    KEY_ARTICLE_TITLE}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    public String getArticleName(int rowId) throws SQLException{
    	Cursor c = fetchArticle(rowId);
    	return c.getString(c.getColumnIndex(KEY_ARTICLE_TITLE));
    }
	
}